{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Flash Flood Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook provides basic information on the flood severity dataset (relevant to task two). This data is taken from the "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import chartify \n",
    "import os\n",
    "import bokeh as bk\n",
    "data_path = os.path.abspath(\"\").split(\"flood_forecast\")[0] +\"data\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID_TAG</th>\n",
       "      <th>START_UTC</th>\n",
       "      <th>START_UNIX</th>\n",
       "      <th>END_UTC</th>\n",
       "      <th>END_UNIX</th>\n",
       "      <th>START_LAT</th>\n",
       "      <th>START_LON</th>\n",
       "      <th>IMPACT1</th>\n",
       "      <th>IMPACT2</th>\n",
       "      <th>IMPACT3</th>\n",
       "      <th>...</th>\n",
       "      <th>FLOOD_EVAC</th>\n",
       "      <th>CONTACT_RE</th>\n",
       "      <th>FLOOD_FREQ</th>\n",
       "      <th>LANDUSE_CO</th>\n",
       "      <th>LAND_USE</th>\n",
       "      <th>POPULATION</th>\n",
       "      <th>SLOPE</th>\n",
       "      <th>FLOW_ACCU</th>\n",
       "      <th>CTI</th>\n",
       "      <th>HU</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9364</th>\n",
       "      <td>1219183208KM</td>\n",
       "      <td>8/19/2008 9:58:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>8/19/2008 9:58:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>33.9030</td>\n",
       "      <td>-98.4806</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Questionable data point - time</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>urban_and_built-up</td>\n",
       "      <td>341.5340</td>\n",
       "      <td>0.19</td>\n",
       "      <td>342.0</td>\n",
       "      <td>11.54</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9365</th>\n",
       "      <td>1219183230TM</td>\n",
       "      <td>8/19/2008 9:59:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>8/19/2008 9:59:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>33.9457</td>\n",
       "      <td>-98.4461</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Questionable data point - time</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>cropland</td>\n",
       "      <td>31.7786</td>\n",
       "      <td>0.56</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.72</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9366</th>\n",
       "      <td>1219183352KM</td>\n",
       "      <td>8/19/2008 10:00:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>8/19/2008 10:00:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>33.9027</td>\n",
       "      <td>-98.4501</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Questionable data point - time</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>grassland</td>\n",
       "      <td>118.3290</td>\n",
       "      <td>0.25</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9367</th>\n",
       "      <td>1219183395KM</td>\n",
       "      <td>8/19/2008 10:02:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>8/19/2008 10:02:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>33.9212</td>\n",
       "      <td>-98.4308</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Questionable data point - time</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>cropland</td>\n",
       "      <td>18.5963</td>\n",
       "      <td>0.36</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.06</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9368</th>\n",
       "      <td>1219183397TM</td>\n",
       "      <td>8/19/2008 10:02:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>8/19/2008 10:02:00 PM</td>\n",
       "      <td>1.219183e+09</td>\n",
       "      <td>33.9815</td>\n",
       "      <td>-98.5284</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>Questionable data point - time</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>cropland</td>\n",
       "      <td>1082.9200</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.13</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID_TAG              START_UTC    START_UNIX  \\\n",
       "9364  1219183208KM   8/19/2008 9:58:00 PM  1.219183e+09   \n",
       "9365  1219183230TM   8/19/2008 9:59:00 PM  1.219183e+09   \n",
       "9366  1219183352KM  8/19/2008 10:00:00 PM  1.219183e+09   \n",
       "9367  1219183395KM  8/19/2008 10:02:00 PM  1.219183e+09   \n",
       "9368  1219183397TM  8/19/2008 10:02:00 PM  1.219183e+09   \n",
       "\n",
       "                    END_UTC      END_UNIX  START_LAT  START_LON  IMPACT1  \\\n",
       "9364   8/19/2008 9:58:00 PM  1.219183e+09    33.9030   -98.4806        1   \n",
       "9365   8/19/2008 9:59:00 PM  1.219183e+09    33.9457   -98.4461        1   \n",
       "9366  8/19/2008 10:00:00 PM  1.219183e+09    33.9027   -98.4501        1   \n",
       "9367  8/19/2008 10:02:00 PM  1.219183e+09    33.9212   -98.4308        1   \n",
       "9368  8/19/2008 10:02:00 PM  1.219183e+09    33.9815   -98.5284        1   \n",
       "\n",
       "      IMPACT2  IMPACT3 ... FLOOD_EVAC                      CONTACT_RE  \\\n",
       "9364        1        1 ...             Questionable data point - time   \n",
       "9365        1        1 ...             Questionable data point - time   \n",
       "9366        1        1 ...             Questionable data point - time   \n",
       "9367        1        1 ...             Questionable data point - time   \n",
       "9368        1        1 ...             Questionable data point - time   \n",
       "\n",
       "     FLOOD_FREQ LANDUSE_CO            LAND_USE  POPULATION SLOPE FLOW_ACCU  \\\n",
       "9364          0         13  urban_and_built-up    341.5340  0.19     342.0   \n",
       "9365          0         11            cropland     31.7786  0.56       2.0   \n",
       "9366          0         10           grassland    118.3290  0.25       1.0   \n",
       "9367          0         11            cropland     18.5963  0.36       0.0   \n",
       "9368          0         11            cropland   1082.9200  0.13       0.0   \n",
       "\n",
       "        CTI  HU  \n",
       "9364  11.54  11  \n",
       "9365   5.72  11  \n",
       "9366   6.11  11  \n",
       "9367   5.06  11  \n",
       "9368   6.13  11  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flood_severity_df = pd.read_csv(os.path.join(data_path,\"usgs_event/shave_all.csv\"))\n",
    "flood_severity_df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take from https://www.nssl.noaa.gov/projects/flash/database/2016v1/shave_impacts_metadata.txt\n",
    "\n",
    "Id_tag\t\t\t\t\n",
    "Denotes the call time and caller (for internal use) [In 2010, this field was changed to serve as a unique report identifier containing a number denoting when each report was taken in chronological order]\n",
    "\n",
    "Start_UTC\t\t\tEvent start date and time in UTC\n",
    "\n",
    "Start_UNIX\t\t\tEvent start time in Unix time\n",
    "\n",
    "End_UTC\t\t\t\tEvent end date and time in UTC\n",
    "\n",
    "End_UNIX\t\t\tEvent end time in Unix time\n",
    "\n",
    "Start_lat\t\t\tLatitude of report\n",
    "\n",
    "Start_lon\t\t\tLongitude of report\n",
    "\n",
    "Impact1\t\t\t\tMost severe recorded impact. \n",
    "\t\t\t\t\n",
    "\t\t\t\tImpact categories are classified from the least to the most severe into the following categories :\n",
    "\t\t\t\t1 = no impact (SHAVE 'null report')\n",
    "\t\t\t\t2 = other (unclassified or unknown impact)\n",
    "\t\t\t\t3 = overflow (streams out of their banks)\n",
    "\t\t\t\t4 = greenlands flooding (inlcudes : cropland, pastures, yards, grassland)\n",
    "\t\t\t\t5 = street/road flooding\n",
    "\t\t\t\t6 = road closure (or impassible)\n",
    "\t\t\t\t7 = inundation (floodwaters in buildings/homes, including basements)\n",
    "\t\t\t\t8 = Evacuation \n",
    "\t\t\t\t9 = Stranded cars (e.g.: moved by floodwaters, stalled in ditches, ...)\n",
    "\t\t\t\t10 = Rescue, Fatality or Injury\n",
    "\n",
    "Impact2\t\t\t\tSecond most severe recorded impact\n",
    "\n",
    "Impact3\t\t\t\tThird most severe recorded impact\n",
    "\n",
    "Metr_comments\t\t\tAdditional comments about the call (pertaining to meteorological events)\n",
    "\n",
    "Report_type\t\t\t2 = severe\n",
    "\t\t\t\t1 = non-severe\n",
    "\t\t\t\t0 = null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IMPACT1</th>\n",
       "      <th>ID_TAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>6872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   IMPACT1  ID_TAG\n",
       "0        1    6872\n",
       "1        2     374\n",
       "2        3     238\n",
       "3        4     962\n",
       "4        5     233\n",
       "5        6     215\n",
       "6        7     371\n",
       "7        8      40\n",
       "8        9      33\n",
       "9       10      31"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "impacted_grouped_df = flood_severity_df.groupby('IMPACT1')\n",
    "count_impacted = impacted_grouped_df[\"ID_TAG\"].count().reset_index()\n",
    "count_impacted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO add bar chart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see the dataset is heavily imbalanced with the majority of events having an impact level of one"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LAND_USE\n",
       "closed_shrubland                 54\n",
       "cropland                       4130\n",
       "deciduous_broadleaf_forest      363\n",
       "evergreen_needleleaf_forest      80\n",
       "grassland                       715\n",
       "mixed_forest                    224\n",
       "open_shrubland                   90\n",
       "urban_and_built-up             1139\n",
       "water                            52\n",
       "wooded_grassland               1793\n",
       "woodland                        729\n",
       "Name: ID_TAG, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flood_severity_df.groupby('LAND_USE')[\"ID_TAG\"].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LAND_USE                     IMPACT1\n",
       "closed_shrubland             1            34\n",
       "                             2             6\n",
       "                             3             3\n",
       "                             4             3\n",
       "                             5             4\n",
       "                             6             1\n",
       "                             7             3\n",
       "cropland                     1          2963\n",
       "                             2           141\n",
       "                             3           114\n",
       "                             4           527\n",
       "                             5           102\n",
       "                             6           109\n",
       "                             7           134\n",
       "                             8            14\n",
       "                             9            11\n",
       "                             10           15\n",
       "deciduous_broadleaf_forest   1           260\n",
       "                             2            17\n",
       "                             3            15\n",
       "                             4            32\n",
       "                             5             9\n",
       "                             6             8\n",
       "                             7            19\n",
       "                             8             2\n",
       "                             10            1\n",
       "evergreen_needleleaf_forest  1            54\n",
       "                             2             7\n",
       "                             3             1\n",
       "                             4            10\n",
       "                                        ... \n",
       "urban_and_built-up           8             8\n",
       "                             9            10\n",
       "                             10            2\n",
       "water                        1            35\n",
       "                             2             4\n",
       "                             3             1\n",
       "                             4             4\n",
       "                             5             1\n",
       "                             7             6\n",
       "                             10            1\n",
       "wooded_grassland             1          1347\n",
       "                             2            64\n",
       "                             3            59\n",
       "                             4           170\n",
       "                             5            38\n",
       "                             6            42\n",
       "                             7            54\n",
       "                             8            11\n",
       "                             9             1\n",
       "                             10            7\n",
       "woodland                     1           512\n",
       "                             2            32\n",
       "                             3            19\n",
       "                             4            83\n",
       "                             5            18\n",
       "                             6            17\n",
       "                             7            38\n",
       "                             8             3\n",
       "                             9             3\n",
       "                             10            4\n",
       "Name: START_UTC, Length: 94, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flood_severity_df.groupby(['LAND_USE', \"IMPACT1\"])['START_UTC'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GaugeID</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "      <th>Start Time (UTC)</th>\n",
       "      <th>End Time (UTC)</th>\n",
       "      <th>Peak Q (cms)</th>\n",
       "      <th>Peak Time (UTC)</th>\n",
       "      <th>Delta Time (h)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4395</th>\n",
       "      <td>1100000</td>\n",
       "      <td>42.645833</td>\n",
       "      <td>-71.298889</td>\n",
       "      <td>2011-03-13 14:00:00</td>\n",
       "      <td>2011-03-14 21:00:00</td>\n",
       "      <td>1039.2283</td>\n",
       "      <td>2011-03-14 03:30:00</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4396</th>\n",
       "      <td>1100000</td>\n",
       "      <td>42.645833</td>\n",
       "      <td>-71.298889</td>\n",
       "      <td>2011-03-19 14:00:00</td>\n",
       "      <td>2011-03-21 11:15:00</td>\n",
       "      <td>1084.5352</td>\n",
       "      <td>2011-03-20 12:45:00</td>\n",
       "      <td>22.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4397</th>\n",
       "      <td>1100000</td>\n",
       "      <td>42.645833</td>\n",
       "      <td>-71.298889</td>\n",
       "      <td>2011-04-18 16:45:00</td>\n",
       "      <td>2011-04-20 01:30:00</td>\n",
       "      <td>1047.7233</td>\n",
       "      <td>2011-04-19 05:15:00</td>\n",
       "      <td>12.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4398</th>\n",
       "      <td>1100000</td>\n",
       "      <td>42.645833</td>\n",
       "      <td>-71.298889</td>\n",
       "      <td>2014-04-01 04:30:00</td>\n",
       "      <td>2014-04-03 00:30:00</td>\n",
       "      <td>1070.3768</td>\n",
       "      <td>2014-04-01 18:00:00</td>\n",
       "      <td>13.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4399</th>\n",
       "      <td>1100000</td>\n",
       "      <td>42.645833</td>\n",
       "      <td>-71.298889</td>\n",
       "      <td>2014-04-10 00:45:00</td>\n",
       "      <td>2014-04-11 02:00:00</td>\n",
       "      <td>1019.4065</td>\n",
       "      <td>2014-04-10 09:45:00</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      GaugeID        Lat        Lon     Start Time (UTC)       End Time (UTC)  \\\n",
       "4395  1100000  42.645833 -71.298889  2011-03-13 14:00:00  2011-03-14 21:00:00   \n",
       "4396  1100000  42.645833 -71.298889  2011-03-19 14:00:00  2011-03-21 11:15:00   \n",
       "4397  1100000  42.645833 -71.298889  2011-04-18 16:45:00  2011-04-20 01:30:00   \n",
       "4398  1100000  42.645833 -71.298889  2014-04-01 04:30:00  2014-04-03 00:30:00   \n",
       "4399  1100000  42.645833 -71.298889  2014-04-10 00:45:00  2014-04-11 02:00:00   \n",
       "\n",
       "       Peak Q (cms)      Peak Time (UTC)   Delta Time (h)  \n",
       "4395      1039.2283  2011-03-14 03:30:00            13.50  \n",
       "4396      1084.5352  2011-03-20 12:45:00            22.75  \n",
       "4397      1047.7233  2011-04-19 05:15:00            12.50  \n",
       "4398      1070.3768  2014-04-01 18:00:00            13.50  \n",
       "4399      1019.4065  2014-04-10 09:45:00             9.00  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "usgs_event_df = pd.read_csv(os.path.join(data_path,\"usgs_event/01.csv\"))\n",
    "usgs_event_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "usgs_meta_df = pd.read_csv(os.path.join(data_path,\"usgs_meta_csv/01.csv\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GaugeID</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "      <th>HUC</th>\n",
       "      <th>Agency</th>\n",
       "      <th>Regulated</th>\n",
       "      <th>Gauge Name</th>\n",
       "      <th>Drainage Area (km2)</th>\n",
       "      <th>Contributing Drainage Area (km2)</th>\n",
       "      <th>Q_2 (cms)</th>\n",
       "      <th>...</th>\n",
       "      <th>Q_10 (cms)</th>\n",
       "      <th>Q_25 (cms)</th>\n",
       "      <th>Q_50 (cms)</th>\n",
       "      <th>Q_100 (cms)</th>\n",
       "      <th>Q_200 (cms)</th>\n",
       "      <th>Q_500 (cms)</th>\n",
       "      <th>Action (cms)</th>\n",
       "      <th>Minor (cms)</th>\n",
       "      <th>Moderate (cms)</th>\n",
       "      <th>Major (cms)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>374</th>\n",
       "      <td>1209700</td>\n",
       "      <td>41.163611</td>\n",
       "      <td>-73.419722</td>\n",
       "      <td>1100006</td>\n",
       "      <td>USGS</td>\n",
       "      <td>Undefined</td>\n",
       "      <td>NORWALK R AT SOUTH WILTON, CT.</td>\n",
       "      <td>77.6996</td>\n",
       "      <td>77.6996</td>\n",
       "      <td>33.9802</td>\n",
       "      <td>...</td>\n",
       "      <td>90.6139</td>\n",
       "      <td>130.2575</td>\n",
       "      <td>164.2377</td>\n",
       "      <td>198.2179</td>\n",
       "      <td>240.6932</td>\n",
       "      <td>339.8022</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>1209761</td>\n",
       "      <td>41.174444</td>\n",
       "      <td>-73.511944</td>\n",
       "      <td>1100006</td>\n",
       "      <td>USGS</td>\n",
       "      <td>Undefined</td>\n",
       "      <td>FIVEMILE RIVER NEAR NEW CANAAN, CT.</td>\n",
       "      <td>2.5900</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376</th>\n",
       "      <td>1209901</td>\n",
       "      <td>41.065556</td>\n",
       "      <td>-73.549722</td>\n",
       "      <td>1100006</td>\n",
       "      <td>USGS</td>\n",
       "      <td>Undefined</td>\n",
       "      <td>RIPPOWAM RIVER AT STAMFORD, CT.</td>\n",
       "      <td>88.0596</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377</th>\n",
       "      <td>4296000</td>\n",
       "      <td>44.868889</td>\n",
       "      <td>-72.270556</td>\n",
       "      <td>1110000</td>\n",
       "      <td>USGS</td>\n",
       "      <td>Undefined</td>\n",
       "      <td>BLACK RIVER AT COVENTRY, VT</td>\n",
       "      <td>315.9785</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>59.7485</td>\n",
       "      <td>...</td>\n",
       "      <td>86.9327</td>\n",
       "      <td>100.8080</td>\n",
       "      <td>111.0020</td>\n",
       "      <td>121.4793</td>\n",
       "      <td>131.9565</td>\n",
       "      <td>146.3981</td>\n",
       "      <td>75.0396</td>\n",
       "      <td>107.8872</td>\n",
       "      <td>-0.0283</td>\n",
       "      <td>-0.0283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>4296500</td>\n",
       "      <td>44.940278</td>\n",
       "      <td>-72.189722</td>\n",
       "      <td>1110000</td>\n",
       "      <td>USGS</td>\n",
       "      <td>Undefined</td>\n",
       "      <td>CLYDE RIVER AT NEWPORT, VT</td>\n",
       "      <td>367.7783</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     GaugeID        Lat        Lon      HUC  Agency  Regulated  \\\n",
       "374  1209700  41.163611 -73.419722  1100006    USGS  Undefined   \n",
       "375  1209761  41.174444 -73.511944  1100006    USGS  Undefined   \n",
       "376  1209901  41.065556 -73.549722  1100006    USGS  Undefined   \n",
       "377  4296000  44.868889 -72.270556  1110000    USGS  Undefined   \n",
       "378  4296500  44.940278 -72.189722  1110000    USGS  Undefined   \n",
       "\n",
       "                              Gauge Name   Drainage Area (km2)  \\\n",
       "374       NORWALK R AT SOUTH WILTON, CT.               77.6996   \n",
       "375  FIVEMILE RIVER NEAR NEW CANAAN, CT.                2.5900   \n",
       "376      RIPPOWAM RIVER AT STAMFORD, CT.               88.0596   \n",
       "377          BLACK RIVER AT COVENTRY, VT              315.9785   \n",
       "378           CLYDE RIVER AT NEWPORT, VT              367.7783   \n",
       "\n",
       "      Contributing Drainage Area (km2)   Q_2 (cms)      ...        Q_10 (cms)  \\\n",
       "374                            77.6996     33.9802      ...           90.6139   \n",
       "375                             0.0000     -1.0000      ...           -1.0000   \n",
       "376                             0.0000     -1.0000      ...           -1.0000   \n",
       "377                             0.0000     59.7485      ...           86.9327   \n",
       "378                             0.0000     -1.0000      ...           -1.0000   \n",
       "\n",
       "      Q_25 (cms)   Q_50 (cms)   Q_100 (cms)   Q_200 (cms)   Q_500 (cms)  \\\n",
       "374     130.2575     164.2377      198.2179      240.6932      339.8022   \n",
       "375      -1.0000      -1.0000       -1.0000       -1.0000       -1.0000   \n",
       "376      -1.0000      -1.0000       -1.0000       -1.0000       -1.0000   \n",
       "377     100.8080     111.0020      121.4793      131.9565      146.3981   \n",
       "378      -1.0000      -1.0000       -1.0000       -1.0000       -1.0000   \n",
       "\n",
       "     Action (cms)   Minor (cms)   Moderate (cms)   Major (cms)  \n",
       "374       -1.0000       -1.0000          -1.0000       -1.0000  \n",
       "375       -1.0000       -1.0000          -1.0000       -1.0000  \n",
       "376       -1.0000       -1.0000          -1.0000       -1.0000  \n",
       "377       75.0396      107.8872          -0.0283       -0.0283  \n",
       "378       -1.0000       -1.0000          -1.0000       -1.0000  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "usgs_meta_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
